﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
    public class DBRoles : IDisposable
    {
        string sql;
        SqlHelpers sqlh;
        DataSet ds;
        SqlDataReader dr;
        public DBRoles()
        {
            sqlh = new SqlHelpers();
        }
        public DBRoles(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }
        /// <summary>
        /// 查询所有角色信息的方法
        /// </summary>
        /// <returns></returns>
        public List<CRoles> DBRolesQu()
        {
            List<CRoles> lur = new List<CRoles>();
            sql = "select * from Roles";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CRoles r = new CRoles();
                r.CrId1 = Convert.ToInt32(dr["rId"]);
                r.CrName1 = dr["rName"].ToString();
                lur.Add(r);
            }
            dr.Close();
            return lur;
        }

        /// <summary>
        /// 插入角色的方法
        /// </summary>
        /// <param name="urName"></param>
        public void DBRolesIN(string rName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 20));
            pars[0].Value = rName;
            sql = "insert intoRoles values(@rName)";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 修改角色信息的方法
        /// </summary>
        /// <param name="ur"></param>
        public void DBRolesUP(int rId, string rName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[0].Value = rId;
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 20));
            pars[1].Value = rName;
            sql = "update UserRoles set rName=@rName where urId=@urId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 删除角色信息的方法
        /// </summary>
        /// <param name="urId"></param>
        public void DBRolesDel(int rId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[0].Value = rId;
            sql = "delete fromRoles where rId=@rId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 根据角色名称查询角色编号的方法
        /// </summary>
        /// <param name="urName"></param>
        /// <returns></returns>
        public int DBUsersID(string rName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 20));
            pars[0].Value = rName;
            sql = "select rId from Roles where rName=@rName";
            return sqlh.ExcuteInsert(sql, pars);
        }

        /// <summary>
        /// 根据角色编号查询相应的权限的方法
        /// </summary>
        /// <param name="urId"></param>
        /// <returns></returns>
        public DataSet DBUsersRoles(int rId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[0].Value = rId;
            sql = "select * from Roles r inner join RolesMenu rm on r.rId=rm.rId inner join menusTiao p on rm.mId=rm.mId where r.rId=@rId";
            ds = sqlh.ExcuteSelect(sql, "URP", pars);
            return ds;
        }

    }
}
